package com.cetc.sdp.kmga.cs.jdbc;

import java.sql.*;
import java.util.Optional;

/**
 * @description:
 * @author： DengQiang
 * @date: 2017/8/21 15:20
 */
public class SQLHelper {

    private static final DruidPool pool = DruidPool.getInstance();

    public static <T> Optional<T> find(String sql, ResultSetFunction<T> fun) {
        try(Connection conn = pool.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql)) {
            return Optional.ofNullable(fun.apply(rs));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Optional.empty();
    }

    public static <T> Optional<T> find(String sql, QueryPreparedStatement ps, ResultSetFunction<T> fun) {
        try(Connection conn = pool.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet rs = ps.fill(stmt)) {
            return Optional.ofNullable(fun.apply(rs));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Optional.empty();
    }

    public static int save(String sql, SqlPreparedStatement ps) {
        try(Connection conn = pool.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql)) {
            ps.fill(stmt);
            return stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static int update(String sql, SqlPreparedStatement ps) {
        return save(sql, ps);
    }

    public static int[] saveBatch(String sql, SqlPreparedStatement ps) {
        try(Connection conn = pool.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql)) {
            ps.fill(stmt);
            return stmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Connection getConnection() throws SQLException {
        return pool.getConnection();
    }

    /**
     * 关闭自动提交
     * @param tc
     * @param <T>
     * @return
     */
    public static <T> Optional<T> executeInTransaction(TransactionConnection<T> tc) {
        Connection connection = null;
        try {
            connection = pool.getConnection();
            connection.setAutoCommit(false);
            T r = tc.doTransaction(connection);
            connection.commit();
            return Optional.ofNullable(r);
        } catch (SQLException e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.setAutoCommit(true);
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return Optional.empty();
    }

    /**
     * SQL ResultSet process function
     * @param <T>
     */
    @FunctionalInterface
    public interface ResultSetFunction<T> {
        T apply(ResultSet rs) throws SQLException;
    }

    @FunctionalInterface
    public interface QueryPreparedStatement {
        ResultSet fill(PreparedStatement stmt) throws SQLException;
    }

    @FunctionalInterface
    public interface SqlPreparedStatement {
        void fill(PreparedStatement stmt) throws SQLException;
    }

    @FunctionalInterface
    public interface TransactionConnection<T> {
        T doTransaction(Connection connection) throws SQLException;
    }
}
